package HtServer.FileUpload.ImportMeetingFromExcel;

import HtServer.RedisTools.RedisToos;
import HtServer.meeting.MeetingItem;
import HtServer.meeting.MeetingItems;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;


import java.io.*;
import java.util.Base64;
import java.util.LinkedList;
import java.util.List;

public class ImportMeetingMenber {

    String filePath;
    public List<MeetingItem> meetingItems = new LinkedList<>();
    public ImportMeetingMenber(String filePath)
    {
        this.filePath = filePath;
    }

    public ImportMeetingMenber()
    {

    }
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        String cellValue = "";
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = cell.getDateCellValue().toString();
                } else {
                    cellValue = Double.toString(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                cellValue = Boolean.toString(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            case BLANK:
                cellValue = "";
                break;
            default:
                cellValue = "Type not supported.";
                break;
        }
        return cellValue;
    }

    public void ImportMeetingMenberCells(InputStream inputStream) throws IOException {
        Workbook workbook = null;

        try {
            workbook = new XSSFWorkbook(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
        int rownum = -1;
        for (Row row : firstSheet) {

            rownum++;
            if(rownum == 0)
            {

                continue;
            }


            MeetingItem mi = new MeetingItem();
            String id = getCellValueAsString(row.getCell(0));
            mi.setId(id);
            //名字
            String cellValue = getCellValueAsString(row.getCell(1));
            mi.setName(cellValue);

            String contents = getCellValueAsString(row.getCell(3));
            mi.setContents(contents);

            String dept = getCellValueAsString(row.getCell(4));
            mi.setDept(dept);

            String groupid = getCellValueAsString(row.getCell(6));
            mi.setGroupid(groupid);
            String groupname = getCellValueAsString(row.getCell(7));
            mi.setGroupname(groupname);

            String order = getCellValueAsString(row.getCell(8));
            mi.setOrder(order);


            Cell cell = row.getCell(9);
            String lingyu = getCellValueAsString(cell);
            mi.setLingyu(lingyu);

            int randomnum = 0;
                    try {

                        double doubleValue = Double.parseDouble(getCellValueAsString(row.getCell(10)));
                        randomnum = (int)doubleValue;
                    }catch (Exception e)
                    {
                        randomnum = 0;
                    }
            mi.setRandomnum(randomnum);



            int randomtype = 0;
            try {

                double doubleValue = Double.parseDouble(getCellValueAsString(row.getCell(11)));
                randomtype = (int)doubleValue;
            }catch (Exception e)
            {
                randomtype = 0;
            }
            mi.setRandomtype(randomtype);


            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle != null) {

                String hexColor = getCellBackgroundColor(cellStyle);

                mi.setColor(hexColor);

            }


//                System.out.println(POIImages.getInstance().images);
            POIImageItem poiImageItem = POIImages.getInstance().getImages(rownum,2);

            if (poiImageItem != null) {
                mi.setImage(poiImageItem.getBase64());
            } else {
                mi.setImage("");
            }


            meetingItems.add(mi);



//                for (Cell cell : row) {
//                    // 获取单元格的值并打印
//                    String cellValue = getCellValueAsString(cell);
//
//
//                    System.out.print(cellValue + "\t");
//                }
//                System.out.println();
        }
    }  finally {
        try {
            if (workbook != null) workbook.close();
            if (inputStream != null) inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    }
    public void MeetingMenberImages(InputStream inputStream){
        try (

             XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) {

            XSSFSheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            List<XSSFShape> shapes = sheet.createDrawingPatriarch().getShapes();

            for (XSSFShape shape : shapes) {
                if (shape instanceof XSSFPicture) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFPictureData pictureData = picture.getPictureData();
                    ClientAnchor anchor = picture.getClientAnchor();

                    System.out.println("图片类型: " + pictureData.getPictureType());
                    System.out.println("图片格式: " + pictureData.suggestFileExtension());
                    System.out.println("图片位置 - Row1: " + anchor.getRow1() + " Col1: " + anchor.getCol1());

                    byte[] bytes = pictureData.getData();
                    String base64 = Base64.getEncoder().encodeToString(bytes);
                    POIImages.getInstance().addImage(new POIImageItem(anchor.getRow1(), anchor.getCol1(), base64));
                } else {
                    System.out.println("shape: " + shape);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();

        }

    }
    public void ImportMeetingMenberCells() throws IOException {
        FileInputStream inputStream = null;
//        Workbook workbook = null;
        inputStream = new FileInputStream(new File(filePath));


        ImportMeetingMenberCells(inputStream);
//        try {
//            inputStream = new FileInputStream(new File(filePath));
//            workbook = new XSSFWorkbook(inputStream);
//            Sheet firstSheet = workbook.getSheetAt(0);
//            int rownum = -1;
//            for (Row row : firstSheet) {
//
//                rownum++;
//                if(rownum == 0)
//                {
//
//                    continue;
//                }
//
//
//                MeetingItem mi = new MeetingItem();
//                String id = getCellValueAsString(row.getCell(0));
//                mi.setId(id);
//                //名字
//                String cellValue = getCellValueAsString(row.getCell(1));
//                mi.setName(cellValue);
//
//                String contents = getCellValueAsString(row.getCell(3));
//                mi.setContents(contents);
//
//                String dept = getCellValueAsString(row.getCell(4));
//                mi.setDept(dept);
//
//                String groupid = getCellValueAsString(row.getCell(6));
//                mi.setGroupid(groupid);
//                String groupname = getCellValueAsString(row.getCell(7));
//                mi.setGroupname(groupname);
//
//                String order = getCellValueAsString(row.getCell(8));
//                mi.setOrder(order);
//
//                String lingyu = getCellValueAsString(row.getCell(9));
//                mi.setLingyu(lingyu);
////                System.out.println(POIImages.getInstance().images);
//                POIImageItem poiImageItem = POIImages.getInstance().getImages(rownum,2);
//
//                if (poiImageItem != null) {
//                    mi.setImage(poiImageItem.getBase64());
//                } else {
//                    mi.setImage("");
//                }
//
//
//                meetingItems.add(mi);
//
//
//
////                for (Cell cell : row) {
////                    // 获取单元格的值并打印
////                    String cellValue = getCellValueAsString(cell);
////
////
////                    System.out.print(cellValue + "\t");
////                }
////                System.out.println();
//            }
//        } catch (IOException ex) {
//            ex.printStackTrace();
//        } finally {
//            try {
//                if (workbook != null) workbook.close();
//                if (inputStream != null) inputStream.close();
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
    }

    public void MeetingMenberImages() {
        try (FileInputStream fis = new FileInputStream(filePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {

            XSSFSheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            List<XSSFShape> shapes = sheet.createDrawingPatriarch().getShapes();

            for (XSSFShape shape : shapes) {
                if (shape instanceof XSSFPicture) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFPictureData pictureData = picture.getPictureData();
                    ClientAnchor anchor = picture.getClientAnchor();

                    System.out.println("图片类型: " + pictureData.getPictureType());
                    System.out.println("图片格式: " + pictureData.suggestFileExtension());
                    System.out.println("图片位置 - Row1: " + anchor.getRow1() + " Col1: " + anchor.getCol1());

                    byte[] bytes = pictureData.getData();
                    String base64 = Base64.getEncoder().encodeToString(bytes);
                    POIImages.getInstance().addImage(new POIImageItem(anchor.getRow1(), anchor.getCol1(), base64));
                } else {
                    System.out.println("shape: " + shape);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();

        }

    }

    public void writeToExcle(OutputStream outputStream) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Data"); // 创建工作表
        int widthInCharacters = 20; // 希望列宽为20个字符
        // 创建表头行
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("人员名字");
        headerRow.createCell(2).setCellValue("人员图片");
        headerRow.createCell(3).setCellValue("内容");
        headerRow.createCell(4).setCellValue("部门");
        headerRow.createCell(5).setCellValue("点赞数量");
        headerRow.createCell(6).setCellValue("组id");
        headerRow.createCell(7).setCellValue("组名");
        headerRow.createCell(8).setCellValue("显示顺序");
        sheet.setColumnWidth(1, 21 * 256);
        sheet.setColumnWidth(2, 21 * 256);
        sheet.setColumnWidth(3, 50 * 256);

        LinkedList<MeetingItem> ll = MeetingItems.getInstance().getLl();
        // 创建绘图工具
        XSSFDrawing drawing = sheet.createDrawingPatriarch();



        // 填充数据
        for (int i = 0; i < ll.size(); i++) {

            MeetingItem meetingItem = MeetingItems.getInstance().getLl().get(i);
            Row row = sheet.createRow(i+1);
            row.setHeight((short)1024);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue(meetingItem.getName());
//                row.createCell(2).setCellValue("");


            row.createCell(3).setCellValue(meetingItem.getContents());
            row.createCell(4).setCellValue(meetingItem.getDept());
            row.createCell(5).setCellValue(meetingItem.getLike());
            row.createCell(6).setCellValue(meetingItem.getGroupid());
            row.createCell(7).setCellValue(meetingItem.getGroupname());
            row.createCell(8).setCellValue(meetingItem.getOrder());





            if (meetingItem.getImage() != null && !meetingItem.getImage().isEmpty()) {
                // 图片插入的单元格
                // 创建锚点设置图片位置
                CreationHelper helper = workbook.getCreationHelper();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(2); // 列A
                anchor.setRow1(i+1); // 行1
                anchor.setCol2(3); // 列B (图片宽度占到第B列)
                anchor.setRow2(i+2); // 行3 (图片高度占到第3行)
                byte[] bytes = Base64.getDecoder().decode(meetingItem.getImage());
                // 插入图片
                int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                XSSFPicture picture = drawing.createPicture(anchor, pictureIdx);
                picture.resize(1); // 自动调整图片大小以适应单元格
            }


        }

        workbook.write(outputStream); // 写入文件
        System.out.println("Excel file was created successfully!");



    }

    /**
     *writeToExcle(new FileOutputStream(new File("/Users/zgw/temp/write2.xlsx")));
     * @deprecated  use writeToExcle(OutputStream outputStream) instead
     * @param filepath
     */
    @Deprecated
    public void writeToExcle(String filepath)
    {
        String excelFilePath = filepath;

        try (XSSFWorkbook workbook = new XSSFWorkbook(); // 创建工作簿
             FileOutputStream fos = new FileOutputStream(excelFilePath)) {
            XSSFSheet sheet = workbook.createSheet("Data"); // 创建工作表
            int widthInCharacters = 20; // 希望列宽为20个字符
            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("ID");
            headerRow.createCell(1).setCellValue("人员名字");
            headerRow.createCell(2).setCellValue("人员图片");
            headerRow.createCell(3).setCellValue("内容");
            headerRow.createCell(4).setCellValue("部门");
            headerRow.createCell(5).setCellValue("点赞数量");
            headerRow.createCell(6).setCellValue("组id");
            headerRow.createCell(7).setCellValue("组名");
            headerRow.createCell(8).setCellValue("显示顺序");
            sheet.setColumnWidth(1, 21 * 256);
            sheet.setColumnWidth(2, 21 * 256);
            sheet.setColumnWidth(3, 50 * 256);

            LinkedList<MeetingItem> ll = MeetingItems.getInstance().getLl();
            // 创建绘图工具
            XSSFDrawing drawing = sheet.createDrawingPatriarch();



            // 填充数据
            for (int i = 0; i < ll.size(); i++) {

                MeetingItem meetingItem = MeetingItems.getInstance().getLl().get(i);
                Row row = sheet.createRow(i+1);
                row.setHeight((short)1024);
                row.createCell(0).setCellValue(i);
                row.createCell(1).setCellValue(meetingItem.getName());
//                row.createCell(2).setCellValue("");


                row.createCell(3).setCellValue(meetingItem.getContents());
                row.createCell(4).setCellValue(meetingItem.getDept());
                row.createCell(5).setCellValue(meetingItem.getLike());
                row.createCell(6).setCellValue(meetingItem.getGroupid());
                row.createCell(7).setCellValue(meetingItem.getGroupname());
                row.createCell(8).setCellValue(meetingItem.getOrder());





                if (meetingItem.getImage() != null && !meetingItem.getImage().isEmpty()) {
                    // 图片插入的单元格
                    // 创建锚点设置图片位置
                    CreationHelper helper = workbook.getCreationHelper();
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(2); // 列A
                    anchor.setRow1(i+1); // 行1
                    anchor.setCol2(3); // 列B (图片宽度占到第B列)
                    anchor.setRow2(i+2); // 行3 (图片高度占到第3行)
                    byte[] bytes = Base64.getDecoder().decode(meetingItem.getImage());
                    // 插入图片
                    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                    XSSFPicture picture = drawing.createPicture(anchor, pictureIdx);
                    picture.resize(1); // 自动调整图片大小以适应单元格
                }


            }

            workbook.write(fos); // 写入文件
            System.out.println("Excel file was created successfully!");
        } catch (IOException e) {
            System.out.println("Error occurred: " + e.getMessage());
        }
    }


    private String getCellBackgroundColor(CellStyle style) {
        Color color = style.getFillForegroundColorColor();
        if (color instanceof XSSFColor) {
            XSSFColor xssfColor = (XSSFColor) color;
            byte[] rgbWithTint = xssfColor.getRGBWithTint(); // 获取包含色调信息的RGB值
            if (rgbWithTint != null) {
                return String.format("#%02X%02X%02X", rgbWithTint[0], rgbWithTint[1], rgbWithTint[2]);
            }
        }
        return "#FFFFFF"; // 默认白色
    }

    public void write2Rides()
    {
        for (MeetingItem meetingItem : meetingItems) {

            meetingItem.saveMeetingItem2Redis();

        }
    }
    public static void main(String[] args) throws IOException {
        RedisToos redisToos = RedisToos.getInstance();
//        redisToos.getJedis().flushDB();
//        example.POI.ImportMeetingMenber importMeetingMenber = new example.POI.ImportMeetingMenber("/Users/zgw/temp/poitest 1.xlsx");
//        importMeetingMenber.MeetingMenberImages();
//        importMeetingMenber.ImportMeetingMenberCells();
//        importMeetingMenber.write2Rides();
//        importMeetingMenber.meetingItems.forEach(System.out::println);
//        System.out.println("POIImages.getInstance().getImages().size() = " + POIImages.getInstance().getImages().size());
//
//        for (MeetingItem meetingItem : importMeetingMenber.meetingItems) {
//            System.out.println("name: " + meetingItem.getName() + " contents: " + meetingItem.getContents() + " dept: " + meetingItem.getDept() + " groupid: " + meetingItem.getGroupid() + " groupname: " + meetingItem.getGroupname() + " order: " + meetingItem.getOrder() + " image: " + meetingItem.getImage());
//            meetingItem.saveMeetingItem2Redis();
//
//        }


/*        //导出例子
        ImportMeetingMenber importMeetingMenber =new ImportMeetingMenber();
//        importMeetingMenber.writeToExcle("/Users/zgw/temp/write.xlsx");
        try {
            importMeetingMenber.writeToExcle(new FileOutputStream(new File("/Users/zgw/temp/write2.xlsx")));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }*/

        //导入例子
      /*  ImportMeetingMenber importMeetingMenber1 = new ImportMeetingMenber("/Users/zgw/temp/write2.xlsx");

        importMeetingMenber1.MeetingMenberImages();
        importMeetingMenber1.ImportMeetingMenberCells();
        importMeetingMenber1.meetingItems.forEach(System.out::println);*/

        XSSFWorkbook workbook = new XSSFWorkbook();
        ImportMeetingMenber importMeetingMenber1 = new ImportMeetingMenber();

//        importMeetingMenber1.writeToExcle("/Users/zgw/temp/write123.xlsx");
        importMeetingMenber1.writeToExcle(new FileOutputStream(new File("/Users/zgw/temp/write23.xlsx")));
       // FileInputStream fileInputStream = new FileInputStream(new File("/Users/zgw/temp/write2.xlsx"));
       // importMeetingMenber1.MeetingMenberImages(fileInputStream);

       // importMeetingMenber1.ImportMeetingMenberCells(fileInputStream);
       // importMeetingMenber1.meetingItems.forEach(System.out::println);
    }
}
